Skip to main content

Filtering

Basic Filtering​

Use WHERE clause to filter messages. Enclose in single quotes the filtering value - see attached example.

tip

WHERE clause uses Boolean values as an entry value. See Operators to learn more about available operators.

Using WHERE for filtering.
#Shell format

==> SELECT * FROM tickquerydemo WHERE symbol == 'XBANK'

>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
0,2011-10-17 17:21:40,XBANK,EQUITY,301.75,40000.0,301.25,800.0
>deltix.timebase.api.messages.TradeMessage,TIMESTAMP,SYMBOL,TYPE,price,size
1,2011-10-17 17:21:41,XBANK,EQUITY,301.25,800.0
>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
2,2011-10-17 17:21:42,XBANK,EQUITY,301.5,60000.0,298.5,800.0
3,2011-10-17 17:21:43,XBANK,EQUITY,299.5,40000.0,295.0,300.0

Filtering by Time​

To filter messages by timestamp, you need to include relational conditions comparing the special field timestamp (or timestampNs) with, typically, a date literal.

info

timestampNs field allows you to filter with nanosecond resolution and is available since 5.6.67+.

Filter by time.
#Shell format

==> SELECT * FROM tickquerydemo WHERE timestamp > '2011-10-17 17:21:40'd

>deltix.timebase.api.messages.TradeMessage,TIMESTAMP,SYMBOL,TYPE,price,size
0,2011-10-17 17:21:41,XBANK,EQUITY,301.25,800.0
>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
1,2011-10-17 17:21:42,XBANK,EQUITY,301.5,60000.0,298.5,800.0
2,2011-10-17 17:21:43,GREATCO,EQUITY,45.0,100.0,43.0,400.0
3,2011-10-17 17:21:43,XBANK,EQUITY,299.5,40000.0,295.0,300.0
>deltix.timebase.api.messages.TradeMessage,TIMESTAMP,SYMBOL,TYPE,price,size
4,2011-10-17 17:21:44,GREATCO,EQUITY,44.0,100.0

==> SELECT * FROM tickquerydemo WHERE timestamp BETWEEN '2011-10-17 17:21:41'd AND '2011-10-17 17:21:42'd

>deltix.timebase.api.messages.TradeMessage,TIMESTAMP,SYMBOL,TYPE,price,size
0,2011-10-17 17:21:41,XBANK,EQUITY,301.25,800.0
>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
1,2011-10-17 17:21:42,XBANK,EQUITY,301.5,60000.0,298.5,800.0

TimeBase will perform timestamp filtering against literals very efficiently, using the internal time index. For example, the above query does not cause TimeBase to read all data from the beginning of the stream, while testing each message against the filter. Instead, TimeBase will use the internal time index to start reading stream at the exact point in the stream where messages with the first timestamp, larger than 2011-10-17 17:21:40, begin.

Meet the below conditions in order for TimeBase to recognize your intent and perform this optimization:

  • Directly compare timestamp with a date literal (or parameter) using a relational operator <, >, <=, >= or between. See Operators to learn more about available operators. See Constants to learn more about available time constants formats.
  • There must be either the only condition of the select statement, or one of several connected by the conjunction operator and conditions.
tip

Unless you specify a timezone all timestamp literals assume GMT timestamp:

Filter selection by the time zone.
SELECT * FROM gaps WHERE timestamp > '2015-03-04 10:55:00 America/New_York'd
  • It does not matter whether timestamp is on the left or right side of the relational operator.
  • You can have any number of conditions connected by and and intermixed with any number of unrelated conditions, as long as time conditions are at the top level.

As mentioned above, you can also use the between expression. Remember that between is an inclusive condition:

Select from the time range using BETWEEN/AND
SELECT * FROM tickquerydemo WHERE timestamp BETWEEN '2011-10-17 17:21:41'd AND '2011-10-17 17:21:42'd
Filter by nanosecond timestamp
SELECT * FROM "BINANCE" WHERE timestampNs > '2024-10-17 17:21:41.000123456'd

Filtering by Object Type​

Use THIS keyword to filter queries by object type.

Select from a specific object.
-- returns all records from TradeMessage object type that meet the condition
SELECT * FROM tickquerydemo WHERE price > 300 AND THIS IS deltix.timebase.api.messages.TradeMessage
Please Note
  • this means current message in this case
  • is <class_name> creates an object type check
  • In type names you can use the dot character without enclosing the entire identifier in quotes.
  • The capitalization of the type name is irrelevant, because QQL performs case-insensitive matching of identifiers, unless existing identifiers differ in case only.

Filter NaN​

Float data types (Float32, Float64, Decimal64) may acquire NaN (not a number), NULL and the actual numeric values.

tip

Any arithmetic operation with NaN gives NaN as a result.

Use operators IS and IS NOT, == and != to filter NaN values.

Check for NaN.
-- Returns true.
SELECT 4 IS NOT nan

-- Returns false.
SELECT 4 IS nan

-- Use in filtering.
SELECT * FROM streamName WHERE fieldName IS NOT nan
tip

Check for NaN includes check for NULL.

Nullability​

Any data field in QQL may be declared as nullable, regardless of its data type. If so declared, it may contain the special out-of-band value of NULL, which basically means "no data". Additionally, NULL values are generated by queries in special cases. A NULL value is formatted as an underscore character: "_", so it can be differentiated from an empty string. Unlike ORACLE, an empty string value is distinctly different from a NULL value.

Array elements can as well be nullable and not nullable.

Skip NULL values.
-- filter out NULL values
SELECT offerPrice AS 'price' FROM tickquerydemo
WHERE symbol == 'GREATCO' AND offerPrice IS NOT NULL

HAVING​

There are cases when filtering needs to be postponed until all expressions have been calculated. This can occur, for example, when the WHERE condition expression affects the result. In such cases, you can use the HAVING clause to apply filtering after the SELECT and WHERE expressions have been calculated.

Let's consider the following example:

SELECT min{}(entries[this is TradeEntry][0].price) as 'min'
FROM "COINBASE" 
OVER TIME(1s)
WHERE symbol == 'BTC/USDT' AND min > 27020

In this case, the WHERE clause is calculated first and depends on the min expression in the SELECT clause. The calculation of the min{}() function will be skipped, which can lead to an unexpected result.

The correct query
SELECT min{}(entries[this is TradeEntry][0].price) as 'min'
FROM "COINBASE" 
OVER TIME(1s)
WHERE symbol == 'BTC/USDT'
HAVING min > 27020

Here is another example:

Calculating the message count for each symbol and filtering by count
SELECT count{}() as c
FROM "COINBASE" 
GROUP BY symbol 
HAVING c > 100000

In this example, the HAVING clause is used to filter the results after the message count for each symbol has been calculated and grouped. This ensures that only the results meeting the specified condition (count being greater than 100,000) are included in the result.